/**
 * 2010(c) Copyright Oceansoft Information System Co.,LTD. All rights reserved.
 * <p>
 * Compile: JDK 1.6+
 * <p>
 * 版权所有(C)：江苏欧索软件有限公司
 * <p>
 * 公司名称：江苏欧索软件有限公司
 * <p>
 * 公司地址：中国苏州科技城青山路1号
 * <p>
 * 网址: http://www.oceansoft.com.cn
 * <p>
 * 版本: 苏州公安统一用户管理平台1.0
 * <p>
 * 作者: 090922(陈伟)
 * <p>
 * 文件名:UUMSRoleDaoImpl.java
 * <p>
 * 类产生时间: 2012-7-11 下午2:37:07
 * <p>
 * 负责人: 090922(陈伟)
 * <p>
 * Email:javacspring@gmail.com
 * <p>
 * 所在组 : 苏州公安统一用户管理平台
 * <p>
 * 所在部门: 电信/国土——技术二部
 * <p>
 * <p>
 */
package com.oceansoft.core.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
import org.springframework.stereotype.Repository;
import org.springframework.util.StringUtils;

import com.oceansoft.core.common.util.PageUtil;
import com.oceansoft.core.common.util.Pagination;
import com.oceansoft.core.dao.IUUMSRoleDao;
import com.oceansoft.core.dao.mapper.UUMSRoleMapper;
import com.oceansoft.core.dao.util.SQLUtil;
import com.oceansoft.core.domain.SysRole;
import com.oceansoft.core.domain.SysUser;

/**
 * 统一用户管理平台角色数据库操作接口实现类
 * 
 * @author chenw
 * 
 */
@Repository
public class UUMSRoleDaoImpl implements IUUMSRoleDao {

	@Resource
	private SimpleJdbcTemplate sjt;

	@Override
	public Pagination<SysRole> findRoleByPage(int pageIndex) {
		String sql_list = "SELECT * FROM T_SYS_ROLE ORDER BY ORDERNUM";
		String sql_count = "SELECT COUNT(1) FROM T_SYS_ROLE";
		int _total = sjt.queryForInt(sql_count);
		List<SysRole> list = sjt.query(PageUtil.getPageSQL(sql_list, pageIndex), new UUMSRoleMapper());
		return new Pagination<SysRole>(list, _total, pageIndex);
	}

	@Override
	public Pagination<SysRole> findRoleByPage(SysRole condition, int pageIndex) {
		StringBuffer sql_list = new StringBuffer("SELECT * FROM T_SYS_ROLE");
		StringBuffer sql_count = new StringBuffer("SELECT COUNT(1) FROM T_SYS_ROLE");
		if (StringUtils.hasText(condition.getRname())) {
			sql_list.append(" WHERE RNAME LIKE '%" + condition.getRname() + "%'");
			sql_count.append(" WHERE RNAME LIKE '%" + condition.getRname() + "%'");
		}
		sql_list.append(" ORDER BY ORDERNUM");
		int _total = sjt.queryForInt(sql_count.toString());
		List<SysRole> list = sjt.query(PageUtil.getPageSQL(sql_list.toString(), pageIndex), new UUMSRoleMapper());
		return new Pagination<SysRole>(list, _total, pageIndex);
	}

	@Override
	public Map<String, Object> findRoleByUser(String policeNum) {
//		String sql = "SELECT T.ROLEID,T1.RNAME FROM T_SYS_ROLE_RESOURCE T LEFT JOIN T_SYS_ROLE T1 ON T.ROLEID = T1.RCODE WHERE T.USERID = ?";
//		final Map<String, Object> map = new HashMap<String, Object>();
//		sjt.query(sql, new RowMapper<SysRole>() {
//			@Override
//			public SysRole mapRow(ResultSet rs, int index) throws SQLException {
//				map.put(rs.getString("ROLEID"), StringUtils.hasText(rs.getString("RNAME")) ? rs.getString("RNAME")
//						: "none");
//				return null;
//			}
//		}, policeNum);
		Map<String, Object> res = new HashMap<String, Object>();
		String sql = "select * from t_sys_user_role t where t.userid=?";
		SQLUtil util = new SQLUtil();
		List<Map<String, Object>> list = util.list(sql,policeNum);
		for (Map<String, Object> object : list) {
			res.put(object.get("ROLEID").toString(), "1");
		}
		return res;
	}

	@Override
	public List<SysUser> findUsersByRoleId(String roleId) {
		String sql = "SELECT T.USERID POLICENUM,U.NAME UNAME,D.NAME DEPTNAME FROM T_SYS_USER U LEFT JOIN T_SYS_USER_ROLE T ON T.USERID = U.POLICENUM LEFT JOIN T_SYS_DEPT D ON U.DEPTID = D.DEPTID WHERE T.ROLEID = ? ORDER BY DEPTNAME";
		List<SysUser> list = sjt.query(sql, new RowMapper<SysUser>() {
			@Override
			public SysUser mapRow(ResultSet rs, int index) throws SQLException {
				return new SysUser(rs.getString("POLICENUM"), rs.getString("UNAME"), rs.getString("DEPTNAME"));
			}
		}, roleId);
		return list;
	}

	@Override
	public int findRoleResourceRowsByRoleId(String roleId) {
		String sql = "SELECT COUNT(1) FROM T_SYS_ROLE_RESOURCE T WHERE T.ROLE_ID = ?";
		return sjt.queryForInt(sql, roleId);
	}

	@Override
	public int findRoleUserRowsByRoleId(String roleId) {
		String sql = "SELECT COUNT(1) FROM T_SYS_USER_ROLE T WHERE T.ROLEID = ?";
		return sjt.queryForInt(sql, roleId);
	}

	@Override
	public void deleteResourceById(String roleId) {
		sjt.update("DELETE FROM T_SYS_ROLE T WHERE T.RCODE = ?", roleId);
	}

	@Override
	public int[] saveRoleUserData(List<Object[]> userRoleData) {
		String sql = "INSERT INTO T_SYS_USER_ROLE(DBID,ROLEID,USERID) VALUES(SEQ_SYS_USER_ROLE.NEXTVAL,?,?)";
		return sjt.batchUpdate(sql, userRoleData);
	}

	@Override
	public void deleteRoleById(String roleId) {
		String sql = "DELETE FROM T_SYS_USER_ROLE WHERE ROLEID = ?";
		sjt.update(sql, roleId);
	}
	
	@Override
	public void deleteRoleUser(String userId,String roleId) {
		String sql = "DELETE FROM T_SYS_ROLE_RESOURCE WHERE ROLEID = ? AND USERID=?";
		sjt.update(sql, roleId, userId);
	}

	@Override
	public SysRole findRoleById(String roleId) {
		String sql = "SELECT * FROM T_SYS_ROLE WHERE RCODE = ?";
		List<SysRole> list = sjt.query(sql, new UUMSRoleMapper(), roleId);
		return list.size() > 0 ? list.get(0) : null;
	}

	@Override
	public void saveRole(SysRole role) {
		String sql = "INSERT INTO T_SYS_ROLE(DBID,RCODE,RNAME,ORDERNUM,ACTIVEFLAG,REMARK,CDATE) VALUES(SEQ_SYS_ROLE.NEXTVAL,SEQ_SYS_ROLE_CODE.NEXTVAL,?,?,1,?,SYSDATE)";
		sjt.update(sql, role.getRname(), role.getOrdernum(), role.getRemark());
	}

	@Override
	public void update(SysRole role) {
		String sql = "UPDATE T_SYS_ROLE SET RNAME =?,ORDERNUM=?,REMARK=?,CDATE=SYSDATE WHERE RCODE=?";
		sjt.update(sql, role.getRname(), role.getOrdernum(), role.getRemark(), role.getRcode());
	}

	@Override
	public void saveRoleResource(List<Object[]> list) {
		String sql = "INSERT INTO T_SYS_ROLE_RESOURCE(DBID,ROLE_ID,RES_ID) VALUES(SEQ_SYS_ROLE_RESOURCE.NEXTVAL,?,?)";
		sjt.batchUpdate(sql, list);
	}

	@Override
	public void deleteRoleResource(String roleId) {
		String sql = "DELETE FROM T_SYS_ROLE_RESOURCE WHERE ROLE_ID = ?";
		sjt.update(sql, roleId);
	}

	@Override
	public int findCountByName(SysRole role) {
		if(Integer.valueOf(role.getRcode())>0){
			String sql = "select count(dbid) from t_sys_role where rname=? and rcode!=?";
			return sjt.queryForInt(sql, role.getRname(),role.getRcode());
		}else{
			String sql = "select count(dbid) from t_sys_role where rname=?";
			return sjt.queryForInt(sql, role.getRname());
		}
	}

	@Override
	public List<SysRole> findAllRoles() {
		String sql_list = "SELECT * FROM T_SYS_ROLE ORDER BY ORDERNUM";
		List<SysRole> list = sjt.query(sql_list, new UUMSRoleMapper());
		return list;
	}

	@Override
	public List<SysRole> findRoles(String policeNum) {
	   String sql="select b.* from t_sys_user_role a,t_sys_role b where a.roleid=b.rcode and a.userid=?";
	   List<SysRole> list = sjt.query(sql, new UUMSRoleMapper(),policeNum);
		return list;
	}
	@Override
	public void delRoleUser(String userid) {
		String sql = "DELETE FROM T_SYS_USER_ROLE WHERE USERID = ?";
		sjt.update(sql, userid);
	}

	@Override
	public void plusAddRoleToUser(String userid, String rolid) {
		String sql = "INSERT INTO T_SYS_USER_ROLE(DBID,ROLEID,USERID) VALUES(SEQ_SYS_USER_ROLE.NEXTVAL,?,?)";
	    sjt.update(sql, rolid,userid);
		
	}
}
